/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.User;

/**
 *
 * @author Hoang Dong Tien
 */
public class UserDAO {

    public static List<User> getAllUsersByCondition(String whereClause) throws ClassNotFoundException, SQLException {
        List<User> users = new ArrayList<User>();
        ResultSet rs = null;
        Connection oconn = DBHandler.getConnection();
        StringBuilder sb = new StringBuilder("SELECT * FROM [Users] ");
        if (whereClause != null) {
            sb.append(whereClause);
        }
        PreparedStatement pstmt = oconn.prepareStatement(sb.toString());
        rs = pstmt.executeQuery();

        User userTmp = null;
        while (rs.next()) {
            userTmp = new User();
            userTmp.setUserId(rs.getInt("UserId"));
            userTmp.setFullname(rs.getNString("Fullname"));
            userTmp.setEmail(rs.getNString("Email"));
            userTmp.setAddress(rs.getNString("Address"));
            userTmp.setUsername(rs.getNString("Username"));
            userTmp.setPassword(rs.getNString("Password"));
            userTmp.setRole(rs.getNString("Role"));
            //System.out.println("Role In DAO : " + rs.getNString("Role"));
            userTmp.setProfileImage(rs.getNString("ProfileImage"));
            userTmp.setBirthday_sql(rs.getDate("Birthday"));
            userTmp.setGender(rs.getNString("Gender"));
            userTmp.setMobile(rs.getNString("Mobile"));

            users.add(userTmp);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);

        return users;
    }

    public static List<User> getAllUsersByCondition() throws ClassNotFoundException, SQLException{
        return getAllUsersByCondition(null);
    }

    public static User getOneUserForLogin(String username, String password) throws ClassNotFoundException, SQLException {
        User user_tmp = null;
        StringBuilder whereClause = new StringBuilder("WHERE ");
        whereClause.append("[Username]='").append(username.replaceAll("'", "''").replaceAll("--", "")).append("'");
        whereClause.append(" AND ");
        whereClause.append("[Password]='").append(password.replaceAll("'", "''").replaceAll("--", "")).append("'");
        //        System.out.println("WHERE : " + whereClause.toString());
        List<User> users = getAllUsersByCondition(whereClause.toString());
        if(!users.isEmpty()){
            user_tmp = users.get(0);
        }
        return user_tmp;
    }

    public static List<User> getUsersByCondition(java.util.Date birthday, String role) throws ClassNotFoundException, SQLException{
        StringBuilder whereClause = new StringBuilder("WHERE ");
        if(birthday != null){
            SimpleDateFormat dtfm = new SimpleDateFormat("yyyy/MM/dd");
            whereClause.append("[Birthday]='").append(dtfm.format(birthday)).append("'");
            whereClause.append(" AND ");
        }

        if(role != null && (role.equals(User.ROLE_ADMIN) || role.equals(User.ROLE_EMPLOYEE))){
            whereClause.append("[role]='").append(role).append("'");
            whereClause.append(" AND ");
        }

        whereClause.append(" 1=1");
        return getAllUsersByCondition(whereClause.toString());
    }

    public static  List<User> getUsersInEvent(int eventId) throws ClassNotFoundException, SQLException{
        StringBuilder suffix = new StringBuilder();
        suffix.append(" FULL JOIN [Requests] ON [Requests].[RequesterUserId] = [Users].[UserId]");
        suffix.append(" FULL JOIN [Events] ON [Event].[EventId] = [Requests].[EventId]");        
        suffix.append(" WHERE [Events].[EventId] = ").append(eventId);
        System.out.println("SELECT * FROM [Users] " + suffix.toString());

        return getAllUsersByCondition(suffix.toString());
    }


    public static int addNewUser(User newuser) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("INSERT INTO [BluePumpkin].[dbo].[Users]([Username],[Password] ,[Fullname] ,[Birthday] ,[Role] ,[Gender] ,[Address] ,[Mobile] ,[Email],[ProfileImage]) VALUES(?,?,?,?,?,?,?,?,?,?)");
        pstmt.setNString(1, newuser.getUsername());
        pstmt.setNString(2, newuser.getPassword());
        pstmt.setNString(3, newuser.getFullname());
        pstmt.setDate(4, newuser.getBirthdaySql());
        pstmt.setNString(5, newuser.getRole());
        pstmt.setNString(6, newuser.getGender());
        pstmt.setNString(7, newuser.getAddress());
        pstmt.setNString(8, newuser.getMobile());
        pstmt.setNString(9, newuser.getEmail());
        pstmt.setNString(10, newuser.getProfileImage());
        result = pstmt.executeUpdate();
        
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }

    public static int updateUser(User newuser) throws ClassNotFoundException, SQLException {
        int result = 0;
        System.out.println(newuser.getUserId());
        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("UPDATE [BluePumpkin].[dbo].[Users] SET [Fullname] = ? ,[Birthday] = ?, [Gender] = ?, [Address]=?,[Mobile] =?,[Email]=? WHERE [UserId]=?");
        pstmt.setNString(1, newuser.getFullname());
        pstmt.setDate(2, newuser.getBirthdaySql());
        pstmt.setNString(3, newuser.getGender());
        pstmt.setNString(4, newuser.getAddress());
        pstmt.setNString(5, newuser.getMobile());
        pstmt.setNString(6, newuser.getEmail());
        pstmt.setInt(7, newuser.getUserId());

        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }

    public static int deleteUser(int userId) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("DELETE FROM [Users] WHERE UserId=?");
        pstmt.setInt(1, userId);
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }

    public static int deleteUser(User user) throws ClassNotFoundException, SQLException {

        return deleteUser(user.getUserId());
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        List<User> users = getUsersByCondition(java.sql.Date.valueOf("1980-08-26"),User.ROLE_EMPLOYEE);
//        List<User> users = getAllUsersByCondition(null);
        for (User user : users) {
            System.out.println("User Name : " + user.getFullname());
        }

        getOneUserForLogin("Usern----ame's", "--'s");
    }
}
